using System;
using System.Collections.Generic;
using System.Text;
using MacomberMap.Common.Internals;
using MacomberMap.Common.Components;
using System.Drawing;
using System.IO;
using System.Data.OleDb;
using System.Xml;
using System.Windows.Forms;
using System.Diagnostics;
using MacomberMap.Common.Types;
using MacomberMap.Common.User_Interfaces.Components;
using MacomberMap.Common.Logging;
using MacomberMap.Common.Events;

namespace MacomberMap.Common.Database_Interface
{
    /// <summary>
    /// This class is responsible for loading and saving coordinates from XML savecases, Excel spreadsheets or CSV files.
    /// </summary>
    public class MM_Coordinate_Interface
    {
        #region Coordinate Loading

        /// <summary>
        /// Load a set of coordinates into memory
        /// </summary>
        /// <param name="FileName"></param>
        public static void LoadCoordinates(String FileName)
        {                                   
            //Depending on our file extension, load the document in appropriately
            String FileExtension = Path.GetExtension(FileName).ToLower();
            if (FileExtension == ".xml" || FileExtension == ".mm_model")
                LoadCoordinatesFromXML(FileName);
            else if (FileExtension == ".csv")
                LoadCoordinatesFromCSV(FileName);
            else if (FileExtension.StartsWith(".xls"))
                LoadCoordinatesFromExcel(FileName);
            else
                throw new InvalidOperationException("Unable to open file " + FileName + ", because its type is not known.");
        }

        /// <summary>
        /// Load our coordinates from an Excel spreadsheet
        /// </summary>
        /// <param name="FileName"></param>
        private static void LoadCoordinatesFromExcel(String FileName)
        {
            String ConnectionString;
            if (Path.GetExtension(FileName).ToLower() == ".xls")
                ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
            else
                ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
            using (OleDbConnection oleConn = new OleDbConnection(ConnectionString))
            {
                oleConn.Open();
                using (OleDbCommand oleCmd = new OleDbCommand("SELECT * FROM [Stations]", oleConn))
                using (OleDbDataReader oleRd = oleCmd.ExecuteReader())
                    while (oleRd.Read())
                    {
                        MM_Substation Sub = new MM_Substation(oleRd);
                        if (!MM_Repository.TEIDs.ContainsKey(Sub.TEID))
                            MM_Repository.TEIDs.Add(Sub.TEID, Sub);
                        if (!MM_Repository.Substations.ContainsKey(Sub.Name))
                            MM_Repository.Substations.Add(Sub.Name, Sub);
                    }
            }
        }

        /// <summary>
        /// Load our coordinates from a CSV file
        /// </summary>
        /// <param name="FileName"></param>
        private static void LoadCoordinatesFromCSV(String FileName)
        {
            foreach (MM_Event EventToRemove in new List<MM_Event>(MM_Repository.Events.Values))
                MM_Log.RemoveEvent(EventToRemove);

            MM_Substation FoundSub;
            FileInfo fI = new FileInfo(FileName);
            using (OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fI.DirectoryName + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited\""))
            {
                oleConn.Open();
                using (OleDbCommand oleCmd = new OleDbCommand("SELECT * FROM [" + fI.Name + "]", oleConn))
                using (OleDbDataReader oleRd = oleCmd.ExecuteReader())
                    while (oleRd.Read())
                    {
                        if (oleRd["Station"] is DBNull == false)
                            if (MM_Repository.Substations.TryGetValue(MM_Repository.TitleCase((string)oleRd["Station"]), out FoundSub))
                            {
                                MM_Log.LogEvent(new MM_Event_ValueChange(FoundSub, FoundSub.LatLong, new PointF(MM_Repository.ToSingle(oleRd["Longitude"]), MM_Repository.ToSingle(oleRd["Latitude"])), "LatLong"));
                                if (FoundSub.LongName != MM_Repository.TitleCase((string)oleRd["LongName"]))
                                    MM_Log.LogEvent(new MM_Event_ValueChange(FoundSub, FoundSub.LongName, MM_Repository.TitleCase((string)oleRd["LongName"]), "LongName"));
                            }
                    }
            }
        }
        
        /// <summary>
        /// Load our coordinates from the XML file into memory
        /// </summary>
        /// <param name="FileName"></param>
        private static void LoadCoordinatesFromXML(String FileName)
        {
            XmlDocument inDoc = new XmlDocument();
            inDoc.Load(FileName);

            //Load in our KVLevels.
            foreach (XmlElement KVLevel in inDoc.SelectNodes("/NetworkModel/Configuration/DisplayParameters/VoltageLevel"))
                MM_Overall_Display.KVLevels.Add(KVLevel.Attributes["Name"].Value, new MM_KVLevel(KVLevel));

            //Go through all elements and load our substations, lines, boundaries and KV levels in.
            foreach (XmlElement BaseElem in inDoc.DocumentElement.ChildNodes)
                if (BaseElem.Name == "Substation")
                    new MM_Substation(BaseElem);
                else if (BaseElem.Name == "Line")
                    new MM_Line(BaseElem);
                else if (BaseElem.Name == "MM_Boundary")
                    new MM_Boundary(BaseElem);
                else if (BaseElem.Name == "KVLevel")
                    MM_Overall_Display.KVLevels.Add(BaseElem.Attributes["Name"].Value, new MM_KVLevel(BaseElem.Attributes["Name"].Value, BaseElem.Attributes["ForeColor"].Value));
        }


        /// <summary>
        /// Load our collection of counties and substation coordinates using our database connector
        /// </summary>
        /// <param name="DatabaseConnector"></param>
        /// <param name="IncludeModel">Whether to include model data</param>
        public static void LoadCoreData(MM_Database DatabaseConnector, bool IncludeModel)
        {   
            //JLH
            RunAndLog(DatabaseConnector, "Loaded element types", "LoadElementTypes");
            RunAndLog(DatabaseConnector, "Loaded KV levels", "LoadKVLevels");
            RunAndLog(DatabaseConnector, "Loaded event types", "LoadEventTypes");
            RunAndLog(DatabaseConnector, "Loaded counties", "LoadCounties");
            if (!IncludeModel)
                return;
            RunAndLog(DatabaseConnector, "Loaded weather and load zones", "LoadZones");
            /*
            RunAndLog(DatabaseConnector, "Loaded companies", "LoadCompanies");
            RunAndLog(DatabaseConnector, "Loaded substations", "LoadSubstations");
            RunAndLog(DatabaseConnector, "Loaded lines", "LoadLines");*/
        }


        /// <summary>
        /// Run a command, then log its completion
        /// </summary>
        /// <param name="DatabaseConnector">The database connector upon which it the subroutine should run</param>
        /// <param name="Descriptor">The descriptor of the task being completed</param>
        /// <param name="TaskToRun">The subroutine name</param>
        private static void RunAndLog(MM_Database DatabaseConnector, String Descriptor, String TaskToRun)
        {
            DateTime StartTime = DateTime.Now;
            DatabaseConnector.GetType().GetMethod(TaskToRun).Invoke(DatabaseConnector, null);
            MM_Log.LogEvent(new MM_Event_Information(Descriptor + ": " + (DateTime.Now - StartTime).ToString(), DatabaseConnector));
        }

        #endregion

        #region Coordinate Saving
        /// <summary>
        /// Save our collection of coordinates
        /// </summary>
        /// <param name="DatabaseConnector"></param>
        public static void SaveCoordinates(MM_Database DatabaseConnector)
        {
            DatabaseConnector.SaveSubstationCoordinates();
        }

        /// <summary>
        /// Save the set of coordinates as requested        
        /// </summary>
        /// <param name="FileName">The outgoing file</param>
        public static void SaveCoordinates(String FileName)
        {
            //Depending on our file extension, load the document in appropriately
            String FileExtension = Path.GetExtension(FileName).ToLower();
            if (FileExtension == ".xml")
                SaveCoordinatesToXML(FileName);
            else if (FileExtension == ".csv")
                SaveCoordinatesToCSV(FileName);
            else if (FileExtension.StartsWith(".xls"))
                SaveCoordinatesToExcel(FileName);
            else
                throw new InvalidOperationException("Unable to open file " + FileName + ", because its type is not known.");
        }

        /// <summary>
        /// Save our coordinates to an XML file
        /// </summary>
        /// <param name="FileName"></param>
        private static void SaveCoordinatesToXML(String FileName)
        {
            XmlTextWriter xW = new XmlTextWriter(FileName, Encoding.UTF8);
            xW.Formatting = Formatting.Indented;
            xW.WriteStartDocument(true);
            xW.WriteStartElement("NetworkModel");
            xW.WriteAttributeString("CreatedOn", XmlConvert.ToString(DateTime.Now, XmlDateTimeSerializationMode.Unspecified));
            xW.WriteAttributeString("CreatedBy", Environment.UserDomainName + "\\" + Environment.UserName);
            foreach (MM_KVLevel KVLevel in MM_Overall_Display.KVLevels.Values)
                KVLevel.WriteXML(xW);                
            MM_Repository.StateBoundary.WriteXML(xW);
            foreach (MM_Boundary Bound in MM_Repository.Counties.Values)
                Bound.WriteXML(xW);
            foreach (MM_Substation Sub in MM_Repository.Substations.Values)
                Sub.WriteXML(xW);
            foreach (MM_Line Line in MM_Repository.Lines.Values)
                Line.WriteXML(xW);
            xW.WriteEndDocument();
            xW.Flush();
            xW.Close();
            MessageBox.Show("The model information has been saved.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        /// <summary>
        /// Save our coordinates to a CSV file
        /// </summary>
        /// <param name="FileName"></param>
        private static void SaveCoordinatesToCSV(String FileName)
        {
            using (StreamWriter sW = new StreamWriter(FileName))
            {
                sW.WriteLine("STATION,LONGNAME,LONGITUDE,LATITUDE");
                foreach (MM_Substation Sub in MM_Repository.Substations.Values)
                    sW.WriteLine("\"{0}\",\"{1}\",{2},{3}", Sub.Name.Trim(), Sub.LongName.Trim(), Sub.LatLong.X, Sub.LatLong.Y);
                sW.Flush();
                sW.Close();
            }
        }

        private static void SaveCoordinatesToExcel(String FileName)
        {
            String ConnectionString;
            if (Path.GetExtension(FileName).ToLower() == ".xls")
                ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
            else
                ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
            using (OleDbConnection oleConn = new OleDbConnection(ConnectionString))
            {
                oleConn.Open();
                using (OleDbCommand oleCmd = new OleDbCommand("CREATE TABLE [Stations] ([STATION] String, [LONGNAME] String, [LONGITUDE] double, [LATITUDE] double)"))
                    oleCmd.ExecuteNonQuery();

                using (OleDbCommand oleCmd = new OleDbCommand("INSERT INTO [Stations] ([STATION],[LONGNAME],[LONGITUDE],[LATITUDE]) VALUES (?Station, ?Longname, ?Longitude, ?Latitude)"))
                {
                    oleCmd.Prepare();
                    foreach (MM_Substation Sub in MM_Repository.Substations.Values)
                    {
                        oleCmd.Parameters.Clear();
                        oleCmd.Parameters.AddWithValue("Station", Sub.Name);
                        oleCmd.Parameters.AddWithValue("LongName", Sub.LongName);
                        oleCmd.Parameters.AddWithValue("Longitude", Sub.LatLong.X);
                        oleCmd.Parameters.AddWithValue("Latitude", Sub.LatLong.Y);
                        oleCmd.ExecuteNonQuery();
                    }
                }
            }
        }
        #endregion

        #region State coordinate handling
        /// <summary>
        /// Rebuild the state boundary from the county boundary collection
        /// </summary>
        /// <param name="dbConnection">The database connection</param>
        public static void RebuildStateBoundary(MM_Database dbConnection)
        {
            //Load in our counties from the database
            dbConnection.LoadCounties();

            //Initialize our temporary and output arrays
            List<PointF> OutPoints = new List<PointF>();
            Dictionary<PointF, MM_PointDetails> PointDetails = new Dictionary<PointF, MM_PointDetails>();
            Dictionary<MM_Boundary, bool> Counties = new Dictionary<MM_Boundary, bool>();

            //Go through all counties and check every point. If it's unique, add it to the collection, otherwise increment its counter
            foreach (MM_Boundary Boundary in MM_Repository.Counties.Values)
            {
                Counties.Add(Boundary, false);
                for (int PtIndex = 0; PtIndex < Boundary.Coordinates.Length; PtIndex++)
                    if (PointDetails.ContainsKey(Boundary.Coordinates[PtIndex]))
                    {
                        if (!PointDetails[Boundary.Coordinates[PtIndex]].Boundary.Contains(Boundary))
                            PointDetails[Boundary.Coordinates[PtIndex]].Boundary.Add(Boundary);
                    }
                    else
                        PointDetails.Add(Boundary.Coordinates[PtIndex], new MM_PointDetails(Boundary, PtIndex));
            }

            //Now create our list of unique points
            Dictionary<PointF, bool> UniquePoints = new Dictionary<PointF, bool>();
            foreach (KeyValuePair<PointF, MM_PointDetails> PointDetail in PointDetails)
                if (PointDetail.Value.Boundary.Count == 1)
                    UniquePoints.Add(PointDetail.Key, false);

            //Now pull the points in by going through each point, and then determining the closest available point            
            Dictionary<PointF, bool>.Enumerator UniqueEnum = UniquePoints.GetEnumerator();
            UniqueEnum.MoveNext();
            PointF TestPoint = UniqueEnum.Current.Key;

            do
            {
                OutPoints.Add(TestPoint);
                TestPoint = ClosestPoint(UniquePoints, TestPoint);

            } while (!TestPoint.IsEmpty);

            MM_Repository.StateBoundary.Coordinates = OutPoints.ToArray();
            dbConnection.SaveStateCoordinates();
        }

        /// <summary>
        /// Find the closest available point to this one.
        /// </summary>
        /// <param name="Points">The collection of unique points</param>
        /// <param name="ComparisonPoint">The reference point</param>
        /// <returns></returns>
        private static PointF ClosestPoint(Dictionary<PointF, bool> Points, PointF ComparisonPoint)
        {
            SortedDictionary<double, PointF> NearPoints = new SortedDictionary<double, PointF>();
            foreach (KeyValuePair<PointF, bool> kvp in Points)
                if (!kvp.Value)
                {
                    double DistanceToCP = Math.Sqrt(Math.Pow(kvp.Key.X - ComparisonPoint.X, 2) + Math.Pow(kvp.Key.Y - ComparisonPoint.Y, 2));
                    while (NearPoints.ContainsKey(DistanceToCP))
                        DistanceToCP += .0000001d;
                    NearPoints.Add(DistanceToCP, kvp.Key);
                }
            if (NearPoints.Count == 0) return PointF.Empty;
            SortedDictionary<double, PointF>.Enumerator DicEnum = NearPoints.GetEnumerator();

            //Find our closest point, flag it, and return it.
            DicEnum.MoveNext();
            Points[DicEnum.Current.Value] = true;
            return DicEnum.Current.Value;
        }

        ///<summary>This code should create a far better rendition of the Texas State border</summary> 
        private class MM_PointDetails
        {
            public List<MM_Boundary> Boundary = new List<MM_Boundary>();
            public int BoundaryIndex;
            public MM_PointDetails(MM_Boundary Boundary, int BoundaryIndex)
            {
                this.Boundary.Add(Boundary);
                this.BoundaryIndex = BoundaryIndex;
            }
        }

        /// <summary>
        /// Upload the state boundary from a comma-delimited (.csv) file.
        /// </summary>
        /// <param name="FileName">The file to be opened</param>
        /// <param name="dbConnection">The database connection</param>
        public static void UploadStateBoundary(string FileName, MM_Database dbConnection)
        {
            if (MM_Repository.StateBoundary == null)
                MM_Repository.StateBoundary = new MM_Boundary();
            MM_Repository.StateBoundary.Name = "STATE";
            FileInfo fI = new FileInfo(FileName);
            String ConnectionString, TableName;
            if (fI.Extension.ToLower() == ".xls")
            {
                ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
                TableName = "State Boundary";
            }
            else if (fI.Extension.ToLower().StartsWith(".xls"))
            {
                ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
                TableName = "State Boundary";
            }
            else
            {
                ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fI.DirectoryName + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited\"";
                TableName = fI.Name;
            }
            using (OleDbConnection oleConn = new OleDbConnection(ConnectionString))
            {
                oleConn.Open();
                List<PointF> Points = new List<PointF>();

                using (OleDbCommand oleCmd = new OleDbCommand("SELECT * FROM [" + TableName + "]", oleConn))
                using (OleDbDataReader oleRd = oleCmd.ExecuteReader())
                    while (oleRd.Read())
                        Points.Add(new PointF(Convert.ToSingle(oleRd["Longitude"]), Convert.ToSingle(oleRd["Latitude"])));
                MM_Repository.StateBoundary.Coordinates = Points.ToArray();
                MM_Repository.StateBoundary.RecomputeExtrema();
            }
            dbConnection.SaveStateCoordinates();
        }
        #endregion

        /// <summary>
        /// Load a GIS shapefile (*.shp) in for a county
        /// </summary>
        /// <param name="FileName">The file name to open</param>
        /// <param name="dbConnection">The database connection</param>
        public static void ConvertCountyCoordinates(string FileName, MM_Database dbConnection)
        {
            //At present, use the DOC SHP2Text application to perform the actual conversions
            ProcessStartInfo WriterInfo = new System.Diagnostics.ProcessStartInfo(MM_Configuration.ShapeConverter, "--spreadsheet " + FileName);
            WriterInfo.RedirectStandardOutput = true;
            WriterInfo.UseShellExecute = false;
            WriterInfo.WorkingDirectory = Path.GetDirectoryName(FileName);
            using (StreamReader sRd = Process.Start(WriterInfo).StandardOutput)
            {
                //Load in the information from the parsed shapefile
                String CountyName = "";
                List<PointF> BoundaryPoints = new List<PointF>();
                while (!sRd.EndOfStream)
                {
                    String[] inLine = sRd.ReadLine().Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);
                    if ((inLine.Length > 0) && (inLine[0] != "id"))
                    {
                        BoundaryPoints.Add(new PointF(float.Parse(inLine[1]), float.Parse(inLine[2])));
                        CountyName = inLine[8];
                    }
                }

                //Locate or create the appropriate county
                MM_Boundary ThisCounty;
                if (BoundaryPoints.Count > 0 && MM_Repository.Counties.ContainsKey(CountyName))
                    ThisCounty = MM_Repository.Counties[CountyName];
                else
                {
                    ThisCounty = new MM_Boundary();
                    ThisCounty.Name = CountyName;
                }

                //Now, update our information to that county, and upload it to the database
                MM_Repository.Counties[CountyName].Coordinates = BoundaryPoints.ToArray();
                MM_Repository.Counties[CountyName].RecomputeExtrema();
                dbConnection.SaveBoundary(MM_Repository.Counties[CountyName]);
            }
        }
    }

}
